﻿using MyConvert;
using MyDateTime;
using MySQLite.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MySQLite.DataHelp
{
    /// <summary>
    /// 文件操作
    /// </summary>
    public class FileHelper
    {
        #region 对外接口

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="fileModel"></param>
        /// <returns></returns>
        public static bool InsertFileInfo(FileModel fileModel)
        {
            string sql = $" insert into T_File(Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark) values('{fileModel.Guid}','{fileModel.FileType}','{fileModel.FileName}','{fileModel.FileHardDisk}','{fileModel.FilePath}','{fileModel.FileLength}','{fileModel.FileTimeSpan}','{fileModel.FileTime}','{fileModel.CollectTime}','{fileModel.ThumbnailFilePath}','{fileModel.ThumbnailFileTime}','{fileModel.ConvertFilePath}','{fileModel.ConvertFileTime}','{fileModel.UploadFileTime}','{fileModel.DeviceNo}','{fileModel.UserNo}','{fileModel.UserName}','{fileModel.DepNo}','{fileModel.DepName}','{fileModel.IsImportant}','{fileModel.IsDelete}','{fileModel.CreateTime}','{fileModel.Remark}') ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="fileModels"></param>
        /// <returns></returns>
        public static bool InsertFileInfos(List<FileModel> fileModels)
        {
            List<string> sqls = new List<string>();
            foreach (FileModel fileModel in fileModels)
            {
                string sql = $" insert into T_File(Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark) values('{fileModel.Guid}','{fileModel.FileType}','{fileModel.FileName}','{fileModel.FileHardDisk}','{fileModel.FilePath}','{fileModel.FileLength}','{fileModel.FileTimeSpan}','{fileModel.FileTime}','{fileModel.CollectTime}','{fileModel.ThumbnailFilePath}','{fileModel.ThumbnailFileTime}','{fileModel.ConvertFilePath}','{fileModel.ConvertFileTime}','{fileModel.UploadFileTime}','{fileModel.DeviceNo}','{fileModel.UserNo}','{fileModel.UserName}','{fileModel.DepNo}','{fileModel.DepName}','{fileModel.IsImportant}','{fileModel.IsDelete}','{fileModel.CreateTime}','{fileModel.Remark}') ";
                sqls.Add(sql);
            }
            bool flag = MySQLiteHelper.Instance.ExecuteSqlsByTran(sqls);
            return flag;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="guids"></param>
        /// <returns></returns>
        public static bool DeleteFileInfo(string guids)
        {
            string sql = $" update T_File set IsDelete =1 where Guid = '{guids}' ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="guids"></param>
        /// <returns></returns>
        public static bool DeleteFileInfos(List<string> guids)
        {
            string sql = $" update T_File set IsDelete =1 where Guid in ('{string.Join("','", guids)}')  ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns></returns>
        public static bool DeleteAllFileInfo()
        {
            string sql = $" update T_File set IsDelete =1 where UploadFileTime<>'' ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns></returns>
        public static bool DeleteFiles()
        {
            string sql = $" DELETE from T_File where IsDelete =1 and strftime('%s', 'now') - strftime('%s', CreateTime) > 30 * 24 * 60 * 60 ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public static FileModel GetFileInfo(string fileGuid)
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark where Guid='{fileGuid}' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            var result = ModelConvert.ConvertToModel<FileModel>(dt);
            return result;
        }

        /// <summary>
        /// 获取未上传的数据
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetNoUploadFileInfos()
        {
            List<FileModel> results = new List<FileModel>();
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark where UploadFileTime<>'' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            results = ModelConvert.ConvertToModels<FileModel>(dt);
            return results;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public static Pages<FileModel> GetFileInfos(Page<FileCondition> page)
        {
            Pages<FileModel> results = new Pages<FileModel>();
            string strWhere = " where f.IsDelete=0 ";
            if (page.PageModel?.IsNotUpload == true)
            {
                strWhere += $" and (f.UploadTime is null or f.UploadTime ='') ";
            }
            if (page.PageModel?.FileType!=0)
            {
                strWhere += $" and f.FileType ='{page.PageModel?.FileType}' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.FileName))
            {
                strWhere += $" and f.FileName like '%{page.PageModel?.FileName}%' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.DeviceNo))
            {
                strWhere += $" and f.DeviceNo ='{page.PageModel?.DeviceNo}' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.UserNo))
            {
                strWhere += $" and f.UserNo ='{page.PageModel?.UserNo}' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.DepNo))
            {
                strWhere += $" and f.DepNo ='{page.PageModel?.DepNo}' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.StartTime))
            {
                strWhere += $" and f.FileTime >='{Convert.ToDateTime(page.PageModel?.StartTime).ToString("yyyy-MM-dd HH:mm:ss")}' ";
            }
            if (!string.IsNullOrEmpty(page.PageModel?.EndTime))
            {
                strWhere += $" and f.FileTime <='{Convert.ToDateTime(page.PageModel?.EndTime).ToString("yyyy-MM-dd HH:mm:ss")}' ";
            }
            results.PageNo = page.PageNo;
            results.PageRow = page.PageRow;
            results.PageFlag = page.PageFlag;
            results.TotalCount = GetCount(strWhere);
            results.SetPageState();
            results.PageModels = GetModels(page.PageFlag, page.PageNo, page.PageRow, strWhere);
            return results;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> SearchOverdueFiles(int day)
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where IsImportant =0 and julianday(datetime('now','localtime'))-julianday(CollectTime) >{day} ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> SearchOverdueFiles(string deviceNo, int day)
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where IsImportant =0 and julianday(datetime('now','localtime'))-julianday(CollectTime) >{day} and DeviceNo='{deviceNo}' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 获取需要转换缩略图的视频文件
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetThumbVideoFiles()
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where FileType in ('1','2') and ThumbnailFilePath='' and ThumbnailFileTime='' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 更新文件生成缩略图状态
        /// </summary>
        /// <param name="fileGuid"></param>
        /// <param name="convertFilePath"></param>
        /// <returns></returns>
        public static bool UpdateThumbFileInfo(string fileGuid, string convertFilePath)
        {
            string sql = $" update T_File set ThumbnailFilePath='{convertFilePath}',ThumbnailFileTime='{MyDateTimeHelper.CurrentSecondDateTime}' where Guid='{fileGuid}' ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 获取需要转换的视频文件
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetConvertVideoFiles()
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where FileType='1' and ConvertFilePath='' and ConvertFileTime='' and ThumbnailFilePath<>'' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 获取需要转换的音频文件
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetConvertAudioFiles()
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where FileType='2' and ConvertFilePath='' and ConvertFileTime='' ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 更新文件转换状态
        /// </summary>
        /// <param name="fileGuid"></param>
        /// <param name="convertFilePath"></param>
        /// <returns></returns>
        public static bool UpdateConvertFileInfo(string fileGuid, string convertFilePath)
        {
            string sql = $" update T_File set ConvertFilePath='{convertFilePath}',ConvertFileTime='{MyDateTimeHelper.CurrentSecondDateTime}' where Guid='{fileGuid}' ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 获取需要上传的文件（不需要转换的）
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetUploadFiles()
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where (UploadFileTime is null or UploadFileTime='') order by CollectTime desc ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 获取需要上传的文件（需要转换的）
        /// </summary>
        /// <returns></returns>
        public static List<FileModel> GetUploadConvertFiles()
        {
            string sql = $" select Guid,FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File where UploadFileTime='' and ((FileType in('1','2') and ConvertFileTime<>'') or (FileType in('1','2') and (UploadFileTime is null or UploadFileTime=''))) order by CollectTime desc ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            return ModelConvert.ConvertToModels<FileModel>(dt);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileGuid"></param>
        /// <param name="uploadTime"></param>
        /// <returns></returns>
        public static bool UpdateUploadFileInfo(string fileGuid, string uploadTime)
        {
            string sql = $" update T_File set UploadFileTime='{uploadTime}' where Guid='{fileGuid}' ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileGuid"></param>
        /// <param name="uploadTime"></param>
        /// <returns></returns>
        public static bool UpdateUploadFileInfo(List<string> fileGuids)
        {
            string sql = $" update T_File set UploadFileTime='' where Guid in ('{string.Join("','", fileGuids)}')  ";
            bool flag = MySQLiteHelper.Instance.ExecuteSql(sql);
            return flag;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> SearchDeviceInfos()
        {
            string sql = " select DISTINCT DeviceNo from T_File ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            if (dt != null)
            {
                var list = ModelConvert.ConvertToModels<FileModel>(dt);
                if (list != null)
                {
                    Dictionary<string, string> result = new Dictionary<string, string>();
                    result.Add("", "全部");
                    foreach (var file in list) 
                    {
                        result.Add(file.DeviceNo, file.DeviceNo);
                    }
                    return result;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> SearchUserInfos()
        {
            string sql = " select DISTINCT UserNo, UserName from T_File ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            if (dt != null)
            {
                var list = ModelConvert.ConvertToModels<FileModel>(dt);
                if (list != null)
                {
                    Dictionary<string, string> result = new Dictionary<string, string>();
                    result.Add("", "全部");
                    foreach (var file in list)
                    {
                        result.Add(file.UserNo, file.UserName);
                    }
                    return result;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> SearchDeptInfos()
        {
            string sql = " select DISTINCT DepNo, DepName from T_File ";
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            if (dt != null)
            {
                var list = ModelConvert.ConvertToModels<FileModel>(dt);
                if (list != null)
                {
                    Dictionary<string, string> result = new Dictionary<string, string>();
                    result.Add("", "全部");
                    foreach (var file in list)
                    {
                        result.Add(file.DepNo, file.DepName);
                    }
                    return result;
                }
            }
            return null;
        }

        #endregion 对外接口

        #region 内部使用

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageFlag"></param>
        /// <param name="pageNo"></param>
        /// <param name="pageRow"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        static List<FileModel>? GetModels(bool pageFlag, int pageNo, int pageRow, string strWhere)
        {
            List<FileModel> results = new List<FileModel>();
            string sql = $" select Guid,case FileType when 1 then '视频' when 2 then '音频' when 3 then '图片' when 4 then '日志' end as FileType,FileName,FileHardDisk,FilePath,FileLength,FileTimeSpan,FileTime,CollectTime,ThumbnailFilePath,ThumbnailFileTime,ConvertFilePath,ConvertFileTime,UploadFileTime,DeviceNo,UserNo,UserName,DepNo,DepName,IsImportant,IsDelete,CreateTime,Remark from T_File f {strWhere} order by f.FileTime desc ";
            if (pageFlag)
            {
                var begin = (pageNo - 1) * pageRow;
                sql += $" limit {begin},{pageRow} ";
            }
            DataTable dt = MySQLiteHelper.Instance.ExecuteSqlForDataTable(sql);
            results = ModelConvert.ConvertToModels<FileModel>(dt);
            if (results != null)
                results.Insert(0, new FileModel() { Guid = "", FileType="文件类型", FileName="文件名",DeviceNo="设备编号", UserName="用户名",DepName="部门名",FileTime="拍摄时间",CollectTime="采集时间",UploadFileTime="上传时间" });
            return results;
        }

        /// <summary>
        /// 获取查询条件下的用户总数量
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        static int GetCount(string strWhere)
        {
            string sql = $" select count(f.Guid) from T_File f {strWhere} ";
            var count = MySQLiteHelper.Instance.ExecuteSqlToInt(sql);
            return count;
        }

        #endregion 内部使用
    }
}
